﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_GetPersonLocations')
	BEGIN
		DROP  Procedure  esf_sso.P_GetPersonLocations
	END

GO


-- esf_sso.P_GetPersonLocations 1151

CREATE Procedure esf_sso.P_GetPersonLocations
	@pPersonID		INT
AS

	SET NOCOUNT ON;
	
	
	SELECT
		L.ID				Loc_ID
		,L.CountryID		Loc_Cnt_ID
		,VC.Name			Loc_Cnt_Name
		,VCt.ID				Loc_City_ID
		,ISNULL (VCt.Name, L.CityName)	Loc_City_Name
		,L.CityName			Loc_CityName
		,L.Name				Loc_Name
		,L.PhoneNumber		Loc_PhoneNumber
		,L.CellPhoneNumber	Loc_CellPhoneNumber
		,L.Address			Loc_Address
	FROM
		esf_sso.Location L
	INNER JOIN
		esf_sso.Person_Location PL ON PL.LocationID = L.ID
	INNER JOIN
		esf_sso.VCountry VC ON VC.ID = L.CountryID
	LEFT JOIN
		esf_sso.VCity VCt ON VCt.ID = L.CityID
	WHERE
		PL.PersonID = @pPersonID
		
GO